﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using quanlynhasach;

namespace quanlynhasach.Linksql
{
    class BanHangsql
    {
        Connection db = new Connection();
        ChiTietBanHang CTBH = new ChiTietBanHang();
        HoaDonBan HD = new HoaDonBan();


        public string SearchDaTaGridSHD()
        {
            int Count = 0;
            string MaHD = "";
            DataTable dt = new DataTable();//tao bang tam de luu
            dt = db.getDS("Select MaHoaDon From HoaDon");
            for (int i = 1; true; i++)
            {
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    if (dt.Rows[j]["MaHoaDon"].ToString() != "HD" + i)
                    {
                        Count++;//dem so lan khac
                    }
                    else
                    {
                        Count = 0;
                        break;
                    }
                }
                if (Count == dt.Rows.Count)// new so lan khac bang so hang cua bang, nghia la khong co dong nao trung thi tu dong add
                {
                    MaHD = "HD" + i;
                    break;
                }
            }
            return MaHD;
        }


        public string SearchDaTaGridSCTHD()
        {
            int Count = 0;
            string SoCTHD = "";
            DataTable dt = new DataTable();//tao bang tam de luu
            dt = db.getDS("Select SoHoaDon From ChiTietHoaDon");
            for (int i = 1; true; i++)
            {
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    if (dt.Rows[j]["SoHoaDon"].ToString() != "CTHD" + i)
                    {
                        Count++;//dem so lan khac
                    }
                    else
                    {
                        Count = 0;
                        break;
                    }
                }
                if (Count == dt.Rows.Count)// new so lan khac bang so hang cua bang, nghia la khong co dong nao trung thi tu dong add
                {
                    SoCTHD = "CTHD" + i;
                    break;
                }
            }
            return SoCTHD;
        }

        public DataTable getCTHD(string SoHD)
        {
            return db.getDS("Select SoHoaDon,HoaDon.MaHoaDon,HoaDon.MaKH,HoaDon.MaNhanVien,ChiTietHoaDon.MaSach,ChiTietHoaDon.SoLuong,DonGia,ThanhTien,Thue from ChiTietHoaDon INNER JOIN HoaDon ON ChiTietHoaDon.MaHoaDon = HoaDon.MaHoaDon and ChiTietHoaDon.MaHoaDon = '" + SoHD + "'");
        }

        public DataTable getCTHD()
        {
            return db.getDS("Select SoHoaDon, ChiTietHoaDon.MaHoaDon, MaSach, SoLuong,DonGia,ThanhTien,DonVi,MaKH,MaNhanVien,Thue from ChiTietHoaDon INNER JOIN HoaDon on ChiTietHoaDon.MaHoaDon = HoaDon.MaHoaDon");
        }

        public string GetMoney(string MaHD)
        {
            string a = "";
            DataTable dt = db.getDS("Select TongTien from HoaDon where MaHoaDon = '" + MaHD + "'");
            a = dt.Rows[0]["TongTien"].ToString();
            return a;
        }

        public string GetNameKH(ComboBox tb)
        {
            string a = "";
            DataTable dt = db.getDS("Select TenKH from KhachHang where MaKH = '" + tb.Text + "'");
            a = dt.Rows[0]["TenKH"].ToString();
            return a;
        }

        public string GetMaKH(string tb)
        {
            string a = "";
            DataTable dt = db.getDS("Select MaKH from KhachHang where TenKH = '" + tb + "'");
            if (dt.Rows.Count > 0)
                a = dt.Rows[0]["MaKH"].ToString();
            else
                a = "N/A";
            return a;
        }

        public bool check(string SoHD,string MaHD)
        {
            if (db.getDS("Select * from ChiTietHoaDon where SoHoaDon ='"+ SoHD + "' and MaHoaDon = '" +MaHD+"'").Rows.Count > 0)
                return true;
            return false;
        }
        public bool XoaCTHD(string SoHoaDon)
        {
            string[] param = { "@SoHoaDon"};
            object[] value = { SoHoaDon};
            string query = "Delete from ChiTietHoaDon where SoHoaDon=@SoHoaDon";
            return db.ExecuteNonQueryPara(query, param, value);
        }

        public bool XoaHD(string MaHoaDon)
        {
            string[] param = { "@MaHoaDon" };
            object[] value = { MaHoaDon };
            string query = "Delete from HoaDon where MaHoaDon=@MaHoaDon";
            return db.ExecuteNonQueryPara(query, param, value);
        }

        public string GetNameNV(string tb)
        {
                string a = "";
                DataTable dt = db.getDS("Select TenNV from NhanVien where MaNV = '" + tb + "'");
                if (dt.Rows.Count > 0)
                    a = dt.Rows[0]["TenNV"].ToString();
                else
                    a = "N/A";
                return a;
        }

        public string GetMaNV(string tb)
        {
            string a = "";
            DataTable dt = db.getDS("Select MaNV from NhanVien where TenNV = '" + tb + "'");
            if (dt.Rows.Count > 0)
                a = dt.Rows[0]["MaNV"].ToString();
            else
                a="N/A";
            return a;
        }

        public bool SuaCTHD(ChiTietBanHang bh)
        {
            string[] param ={"@SoHD","@MaSach","@SoLuong","@DonGia","@DonVi","@ThanhTien","@Thue"};
            object[] value = { bh.SoCTHD,bh.MaSachCTHD,bh.SoLuongCTHD,bh.DonGiaCTHD,bh.DonViCTHD,bh.ThanhTienCTHD,bh.Thue};
            string query = "Update ChiTietHoaDon"
                           + " set  MaSach=@MaSach,SoLuong=@Soluong,DonGia = @DonGia,"
                           + " DonVi=@DonVi,ThanhTien = @ThanhTien,Thue=@Thue"
                           + " where SoHoaDon=@SoHD";
            return db.ExecuteNonQueryPara(query, param, value);
        }

        public bool ThemHD(HoaDonBan hdb)
        {
            string[] param ={"@MaHD","@MaNV","@TongTien","@Ngay",
                                "@MaKH"};
            object[] value = {hdb.MaHD,hdb.MaNVHD,hdb.TongTienHD,hdb.NgayHD,hdb.MaKHHD};
            string query = "Insert into HoaDon"
                           + "(MaHoaDon,MaNhanVien,TongTien,Ngay,MaKH)"
                           + " values (@MaHD,@MaNV,@TongTien,@Ngay,@MaKH)";
            return db.ExecuteNonQueryPara(query, param, value);
        }

        public bool ThemCTHD(ChiTietBanHang bh)
        {
            string[] param ={"@SoHD","@MaHD","@MaSach","@SoLuong","@DonGia",
                                "@ThanhTien","@DonVi","@Thue"};
            object[] value = { bh.SoCTHD, bh.MaHD, bh.MaSachCTHD, bh.SoLuongCTHD, bh.DonGiaCTHD, bh.ThanhTienCTHD, bh.DonViCTHD, bh.Thue};
            string query = "Insert into ChiTietHoaDon"
                           + "(SoHoaDon,MaHoaDon,MaSach,SoLuong,DonGia,ThanhTien,DonVi,Thue)"
                           + " values (@SoHD,@MaHD,@MaSach,@SoLuong,@DonGia,@ThanhTien,@DonVi,@Thue)";
            return db.ExecuteNonQueryPara(query, param, value);
        }
    }
}
